-- @author lixiyong01
-- @date 2023.03.14
-- 健康分报警工单
-- 2023.08.19 【已下线】

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score_ticket partition (dt=${dateSuffix})
select
    concat(from_unixtime(unix_timestamp(),'yyyyMMddHHmmss'),cast(rand() * 100000000 as int)) as event_id,
    category,
    owner_id as responser,
    '健康分过低告警' as subject,
    concat('您的',category,'当前',total_score,'分，已经连续',state_times,'天低于85分，请尽快处理，该规范2023.06.01正式执行。') as description,
	team,tag
from (
    select
        case
            when dimension='tag,project' then '项目健康分'
            when dimension='tag,team' then '团队健康分'
            when dimension='tag,team,owner_id' then '个人健康分'
            else '其他健康分'
        end as category,
        tag,team,owner_id,total_score,state_times,
        substr(date_add(FROM_UNIXTIME(UNIX_TIMESTAMP()),7-state_times),1,10) as state_dt
    from hdp_lbg_supin_zplisting.app_zp_rock_health_score_state
    where dt='${#date(0, 0, -1):yyyyMMdd#}'
) t;